package com.example.g5project.repository.dao;

import com.example.g5project.entity.Consultant;
import com.example.g5project.entity.frontEndObject.ConsultantIDwithConsultNumber;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;

/**
 * @author Hu Zirui
 * @version 1.0.0
 * @ClassName ConsultantIDwithConsultNumberDao.java
 * @Description TODO
 * @createTime 2022年04月03日 21:53:00
 */
@Repository
@Transactional
@SuppressWarnings("all")
public interface ConsultantIDwithConsultNumberDao extends JpaRepository<Consultant, Integer>, JpaSpecificationExecutor {
    @Query(value = "select chat.counselor_id as consultant_id, count(*) as monthNumber from chat,consultant where MONTH(chat.start_time) = ?2 and consultant.consultant_id\n" +
            "= chat.counselor_id group by chat.counselor_id order by count(*) desc limit ?1", nativeQuery = true)
    ArrayList<ConsultantIDwithConsultNumber> getConsultantByConsultNumber(int k, int month);
}
//    select chat.counselor_id as consultant_id, count(*) as monthNumber from chat,consultant where MONTH(chat.start_time) = 4 and consultant.consultant_id = chat.counselor_id group by chat.counselor_id order by count(*) desc limit 4;